package com.bfsuol.bfsuolframework.core.utils;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import javax.jms.Session;
import javax.management.Query;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import java.util.TreeMap;


/**
 * Excel生成工具类
 * @author caoxl
 */
public class ExcelUtils {

	private static ExcelUtils instance = new ExcelUtils();
	
	/**
	 * 生成一个Excel文件写入类
	 * @return
	 */
	public static ExcelWriter genExcel(){
		return instance.newExcel();
	}
	/**
	 * 生成一个Excel文件写入类
	 * @param session Hibernate的连接代理类
	 * @param hql HQL语句
	 * @return
	 */
	public static ExcelWriter genExcel(Session session, String hql){
		return instance.newExcel(session, hql);
	}

	/**
	 * 生成一个Excel文件写入类
	 * @param list 需要写入的数据
	 * @return
	 */
	public static ExcelWriter genExcel(List<?> list){
		return instance.newExcel(list);
	}
	/**
	 * 创建一个Excel文件写入类
	 * @return
	 */
	private ExcelWriter newExcel(){
		ExcelWriter w = new ExcelWriter();
		return w;
	}
	/**
	 * 生成一个Excel文件写入类
	 * @param session Hibernate的连接代理类
	 * @param hql HQL语句
	 * @return
	 */
	private ExcelWriter newExcel(Session session, String hql){
		ExcelWriter w = new ExcelWriter(session,hql);
		return w;
	}
	/**
	 * 生成一个Excel文件写入类
	 * @param list 需要写入的数据
	 * @return
	 */
	private ExcelWriter newExcel(List<?> list){
		return new ExcelWriter(list);
	}
	
	public static void main(String[] args){
		List<Object> list = new ArrayList<Object>();
		String fileName = genExcel(list).addMethod("title", "标题")
			.addMethod("createDate", "创建时间")
			.setFileName("text.xls")
			.setFilePath("d:/excel")
			.setSheetName("sheet")
			.write();
		System.out.println(fileName);
	}
	
	
	
	/**
	 * 内部类，负责写excel
	 * @author Administrator
	 *
	 */
	public class ExcelWriter{
		private ArrayList<String> columns=new ArrayList<String>();
		private TreeMap<String,String> methods = new TreeMap<String,String>();
		private List<?> dataList;
		private String filePath;
		private String fileName;
		private String sheetName = "sheet1";
		
		public ExcelWriter(){}
		public ExcelWriter(Session session,String hql){
			setHql(session,hql);
		}
		public ExcelWriter(List<?> dataList){
			setDataList(dataList);
		}
		
		public TreeMap<String, String> getMethods() {
			return methods;
		}
		public ArrayList<String> getColumns() {
			return columns;
		}
		public List<?> getDataList() {
			return dataList;
		}
		public String getFilePath() {
			return filePath;
		}
		public String getFileName() {
			return fileName;
		}
		public String getSheetName() {
			return sheetName;
		}
		public ExcelWriter setHql(Session session,String hql){
			try {
				//fixme 待续
				/*Query query = session.createQuery(hql);
				setDataList(query.list());list*/
			}catch (Exception e) {
				e.printStackTrace();
			}
			return this;
		}
		public ExcelWriter addMethod(String methodName, String methodTitle) {
			this.columns.add(methodName);
			this.methods.put(methodName, methodTitle);
			return this;
		}
		public ExcelWriter setDataList(List<?> dataList) {
			this.dataList = dataList;
			return this;
		}
		public ExcelWriter setFilePath(String filePath) {
			this.filePath = filePath;
			return this;
		}
		public ExcelWriter setFileName(String fileName) {
			this.fileName = fileName;
			return this;
		}
		public ExcelWriter setSheetName(String sheetName) {
			this.sheetName = sheetName;
			return this;
		}

		@SuppressWarnings("deprecation")
		public String write(){
			String fullName = null;
			FileOutputStream fo =  null;
			try {
				
				
				FileUtils.createFolder(filePath);
				fullName = filePath + (filePath.endsWith("/")?"":"/") + fileName;
				File desFile = new File(fullName);
				while (desFile.exists()) {
					fullName = FileUtils.getNextFile(fullName);
					desFile = new File(fullName);
				}
				//System.out.println("开始写excel文件 filename="+fullName);
				HSSFWorkbook wb  =   new  HSSFWorkbook();
			    HSSFSheet sheet  =  wb.createSheet(sheetName);
			    
			    int cell_index = 0;
			    HSSFRow row  =  sheet.createRow(( short )0);
			    HSSFCell cell;
			    for(String key:columns){
			    	cell  =  row.createCell(( short )cell_index);
			    	//cell.setEncoding(( short )1);
			    	cell.setCellType( 1 );
			    	String str=methods.get(key);
			    	if(str==null){
			    		cell.setCellValue("");
			    	}else{
			    		cell.setCellValue(str);
			    	}
			    	
			    	cell_index++;
				}
			    
			    int row_index = 1;

			    for(Object obj:dataList){
					row  =  sheet.createRow(( short )row_index);
					cell_index = 0;
					for(String key:columns){
						cell  =  row.createCell(( short )cell_index);
				    	//cell.setEncoding(( short )1);
				    	cell.setCellType( 1 );
				    	Object str=ReflectUtils.getValue(obj,key);
				    	if(str!=null){
				    		cell.setCellValue(String.valueOf(str));
				    	}else{
				    		cell.setCellValue("");
				    	}
				    	cell_index++;
					}
					row_index++;
				}
				
			    fo=new FileOutputStream(desFile);
			    wb.write(fo);

			} catch (Exception e) {
				e.printStackTrace();
				return null;
			} finally{
				if(fo != null){
					try {
						fo.close();
					} catch (IOException e) {}
				}
			}
			return fullName;
		}
		
	}
}
